package com.example.demo.mysqlTest;

import java.sql.*;

/**
 * 删除指定数据库的租户垃圾数据
 *
 * @author LHH
 * @date 2024/12/26
 */
public class mysqlTest {
    // 数据库连接配置
    private static final String DB_URL = "jdbc:mysql://192.168.0.19:3306/saas_startup_dev?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true"; // 请替换为你的数据库名
    private static final String USER = "root"; // 请替换为你的数据库用户名
    private static final String PASSWORD = "lowcodE@33.js"; // 请替换为你的数据库密码

    /**
     * 删除指定数据库的租户垃圾数据
     * @param args
     */
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ResultSet columnRs = null;

        try {
            // 1. 连接到数据库
            conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);
            stmt = conn.createStatement();

            // 2. 获取所有表名
            String query = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'saas_startup_dev';";
            rs = stmt.executeQuery(query);

            // 3. 对每个表执行删除操作
            while (rs.next()) {
                String tableName = rs.getString("table_name");

                // 4. 检查表是否包含tenantId字段
                String columnQuery = "SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema = 'saas_startup_dev' AND table_name = '" + tableName + "';";
                columnRs = stmt.executeQuery(columnQuery);
                boolean hasTenantId = false;
                while (columnRs.next()) {
                    String columnName = columnRs.getString("COLUMN_NAME");
                    if ("tenant_id".equals(columnName)) {
                        hasTenantId = true;
                        break;
                    }
                }

                // 5. 如果表中包含tenantId字段，执行删除操作
                if (hasTenantId) {
                    String deleteQuery = "DELETE FROM " + tableName + " WHERE tenant_id NOT IN (0, 1, 67) ;";

                    try {
                        // 执行删除语句
                        stmt.executeUpdate(deleteQuery);
                        System.out.println("从表中删除的行: " + tableName);
                    } catch (SQLException e) {
                        System.err.println("从表中删除数据出错 " + tableName + ": " + e.getMessage());
                    }
                } else {
                    System.out.println("Table " + tableName + " does not have 'tenantId' column, skipping.");
                }

                // 6. 重置 ResultSet
                columnRs.close();
                columnRs = stmt.executeQuery(columnQuery);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 7. 关闭连接和资源
            try {
                if (rs != null) rs.close();
                if (columnRs != null) columnRs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
